package com.libraryWeb.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.libraryWeb.entity.Admin;
import com.libraryWeb.util.JDBCUtil;



public class AdminDao {
	public int insert(Admin admin) {
		  int flag = -1;
		  //访问数据库  MySQL  
		  Connection conn = null;
		  PreparedStatement stat = null;//SQL 注入   性能更好
		  try { 
			//2建立连接(Connection) 
			conn = JDBCUtil.getConnection();
			 //3创建执行SQL的语句(Statement)
			//stat = conn.createStatement();
			String sql = "INSERT INTO admin VALUES(DEFAULT,?,?,NULL)";
			stat = conn.prepareStatement(sql);
			stat.setString(1, admin.getUserName());
			stat.setString(2, admin.getPwd());
			 //4执行语句
			flag = stat.executeUpdate();//不能传sql  切记！！
		}catch (SQLException e) {
			e.printStackTrace();
		}finally {
			//6释放资源
		    JDBCUtil.close(conn, stat, null);
		}
		return flag;
	}
	
  public int update(Admin admin) {
  	  int flag = -1;
		  //访问数据库  MySQL  
		  Connection conn = null;
		  //Statement stat = null;
		  PreparedStatement stat = null;//SQL 注入   性能更好
		  try { 
			//2建立连接(Connection) 
			conn = JDBCUtil.getConnection();
			 //3创建执行SQL的语句(Statement)
			String sql = "UPDATE admin SET userName=?,pwd=? WHERE id=?";
			stat = conn.prepareStatement(sql);
			stat.setString(1, admin.getUserName());
			stat.setString(2, admin.getPwd());
			stat.setInt(3, admin.getId());
			 //4执行语句
			flag = stat.executeUpdate();//不能传sql  切记！！
		}catch (SQLException e) {
			e.printStackTrace();
		}finally {
			//6释放资源
		    JDBCUtil.close(conn, stat, null);
		}
		return flag;
		
		
	}
  
  public int delete(int id) {
  	  int flag = -1;
		  //访问数据库  MySQL  
		  Connection conn = null;
		  //Statement stat = null;
		  PreparedStatement stat = null;//SQL 注入   性能更好
		  try { 
			//2建立连接(Connection) 
			conn = JDBCUtil.getConnection();
			 //3创建执行SQL的语句(Statement)
			String sql = "DELETE FROM admin WHERE id = ?";
			stat = conn.prepareStatement(sql);
			stat.setInt(1, id);
			 //4执行语句
			flag = stat.executeUpdate();//不能传sql  切记！！
		}catch (SQLException e) {
			e.printStackTrace();
		}finally {
			//6释放资源
		    JDBCUtil.close(conn, stat, null);
		}
		return flag;
	}
  
  public List<Admin> query(){
  	 //访问数据库  MySQL  
		  Connection conn = null;
		  Statement stat = null;
		  ResultSet rs = null;
		  List<Admin> list = new ArrayList<Admin>();
		  try {
			 //1注册驱动 (只做一次)
			//2建立连接(Connection) 
			conn = JDBCUtil.getConnection();
			 //3创建执行SQL的语句(Statement)
			stat = conn.createStatement();
			 //4执行语句
			String sql = "SELECT * FROM admin";
			rs = stat.executeQuery(sql);
			 //5处理执行结果(ResultSet)
			while(rs.next()) {
				Admin admin = new Admin();
				int id = rs.getInt("id");
				admin.setId(id);
				String userNames = rs.getString("userName");
				admin.setUserName(userNames);
				String pwds = rs.getString("pwd");
				admin.setPwd(pwds);
				Date crtTime = rs.getDate("crtTime");
				admin.setCrtTime(crtTime);
				list.add(admin);
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}finally {
			//6释放资源
		    JDBCUtil.close(conn, stat, rs);
		}
		 
    return list;
  }
 /**
         * 根据用户名和密码查询
  * @param userName
  * @param pwd
  * @return
  */
 public Admin query(String userName,String pwd){
	     //访问数据库  MySQL  
		  Connection conn = null;
		  Statement stat = null;
		  ResultSet rs = null;
		  Admin admin = null;
		  try {
			 //1注册驱动 (只做一次)
			//2建立连接(Connection) 
			conn = JDBCUtil.getConnection();
			 //3创建执行SQL的语句(Statement)
			stat = conn.createStatement();
			 //4执行语句
			String sql = "SELECT * FROM admin WHERE userName='"+userName+"' AND pwd='"+pwd+"'";
			rs = stat.executeQuery(sql);
			 //5处理执行结果(ResultSet)
			while(rs.next()) {
				admin = new Admin();
				int id = rs.getInt("id");
				admin.setId(id);
				String userNames = rs.getString("userName");
				admin.setUserName(userNames);
				String pwds = rs.getString("pwd");
				admin.setPwd(pwds);
				Date crtTime = rs.getDate("crtTime");
				admin.setCrtTime(crtTime);
				System.out.println(id+"--"+userNames+"--"+pwds+"--"+crtTime.toLocaleString());
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}finally {
			//6释放资源
		    JDBCUtil.close(conn, stat, rs);
		}
		 
      return admin;
  }
}
